package com.gljx.web.controller;

import com.gljx.util.*;
import com.gljx.web.Entity.*;
import com.gljx.web.dao.StudentMapper;
import com.gljx.web.service.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.*;

import static com.gljx.util.BigDecimalUtil.NullToBigDecimal;

/**
 * Created by Administrator on 2017/11/20 0020.
 */
@Controller()
@RequestMapping("/")
public class SharecarsubentryContraller {

    @Autowired
    DropexerciseThreeService dropexerciseThreeService;
    @Autowired
    DepartmentService departmentService;
    @Autowired
    CustomLogService customLogService;
    @Autowired
    ExerciseCarPlanController exerciseCarPlanController;
    @Autowired
    SharecarsubService sharecarsubService;
    @Autowired
    StudentController studentController;
    @Autowired
    PersonController personController;
    @Autowired
    StudentMapper studentMapper;
    @Autowired
    PersonService personService;
    @Autowired
    StudentService studentService;

    private ImportExecl poi = new ImportExecl();

    /**
     * 自动分车功能
     **/
    @RequestMapping("/SharecarsubentryAddList/look.action")
    public String sharecarsubentryAddList(HttpServletRequest request) throws Exception {


        return "/backHome/practiseCAR/Sharecarsubentry/SharecarsubentryAddList";
    }

    /**
     * @Author lcx
     * @Date 2017/12/4 0004 下午 3:24
     * 在线分车 cflcsjid 练车时间id  carType 车型 number 人数 kemu 科目
     */
    @RequestMapping(value = "/addSharecarsubentry")
    @ResponseBody
    public Object addSharecarsubentry(HttpServletRequest request, String cflcsjid, String carType,
                                      String number, String kemu, String cfcoachid) throws Exception {


        //进行自动分车
        return sharecarsubService.autoCar(cflcsjid, carType, number, kemu, cfcoachid);
    }


    /*  练车预约(批量)*/

    /**
     * @Author lcx
     * @Date 2017/12/8 0008 上午 8:46
     * 批量预约
     */
    @RequestMapping("/practiseOrderAddList")
    public String practiseOrderAddList(HttpServletRequest request, String alertType) throws Exception {
        request.setAttribute("alertType", alertType);

        return "/backHome/orderManage/practiseOrder/practiseOrderListMass";
    }

    /**
     * @Author lcx
     * @Date 2018/2/28 0028 上午 10:35
     * 练车时间id cflcsjid
     */
    @RequestMapping("/practiseOrderAdd")
    public String practiseOrderAdd(HttpServletRequest request, String cfdycs,
                                   String cflcsjid, String cfbilltype, String cfsqzjcxdh, String cfqty) throws Exception {

        //按照练车时间查询预约列表 练车时间 cflcsjid 预约类型 cfbilltype 科目 cfsqzjcxdh type为1则查询预约成功的入 2 反之
        List<Sharecarsub> sharecarsubList = sharecarsubService.selectpractiseOrderList(cflcsjid, cfbilltype, cfsqzjcxdh, "1");
        List<Sharecarsub> sharecarsubList2 = sharecarsubService.selectpractiseOrderList(cflcsjid, cfbilltype, cfsqzjcxdh, "2");
        //上一个界面传入的数据回填

        SharecarsubView sharecarsubView = new SharecarsubView();//视图对象
        //查询其中初考人数 补考人数  退练人数
        int cksize = 0;//初考人数
        int bksize = 0;//补考人数
        int tlsize = 0;//退练人数

        for (Sharecarsub tem : sharecarsubList) {
            //如果考试次数为空认为是初考
            if (tem.getCfkscs() == null || "".equals(tem.getCfkscs()) || new Long(0).equals(tem.getCfkscs())) {
                cksize++;
            } else {
                bksize++;
            }
            if (!"0".equals(tem.getTlcs())) {
                tlsize++;
            }
        }
        sharecarsubView.setCfdycs(cfdycs);//练车时间
        sharecarsubView.setCflcsjid(cflcsjid);//练车时间id
        sharecarsubView.setCfbilltype(cfbilltype);//预约类型
        sharecarsubView.setCfsqzjcxdh(cfsqzjcxdh);//车型
        sharecarsubView.setCfqty(cfqty);//班次人数
        sharecarsubView.setCksize(cksize + "");
        sharecarsubView.setBksize(bksize + "");
        sharecarsubView.setTlsize(tlsize + "");

        sharecarsubView.setYysize(sharecarsubList.size() + "");
        request.setAttribute("sharecarsubView", sharecarsubView);
        request.setAttribute("sharecarsubList", sharecarsubList);
        request.setAttribute("sharecarsubList2", sharecarsubList2);
        request.setAttribute("beginTime", TimeFormat.getMonth());
        request.setAttribute("endTime", TimeFormat.getNowTime());
        request.getSession().setAttribute("sharecarsubView", sharecarsubView);

        return "/backHome/orderManage/practiseOrder/detailpractiseOrderMass";
    }

    @RequestMapping(value = "/add_practiseOrder")
    public String addPractiseOrder(HttpServletRequest request, String cflcsjid,
                                   @RequestParam(defaultValue = "0") String state,
                                   @RequestParam(defaultValue = "2") String cfsubject,
                                   @RequestParam(defaultValue = "") String delFid
    ) {
        Sharecarsub sharecarsub = new Sharecarsub();
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        //公司id
        sharecarsub.setCfficompanyid(user.getFcompanyid());
        //控制单元id
        sharecarsub.setFcontrolunitid(user.getFcompanyid());
        //获取当前修改时间
        String createTime = TimeFormat.timeFormat(new Date());
        //创建时间
        sharecarsub.setFcreatetime(createTime);
        //创建人id
        sharecarsub.setFcreatorid(user.getFid());

        sharecarsub.setCfsubject(Long.valueOf(cfsubject));

        //获取子类
        String[] delfid = delFid.split(",");
        String[] fid = request.getParameterValues("check");
        String[] cfstudentname = request.getParameterValues("cfstudentname");//学生id
        String[] cfstudentid = request.getParameterValues("cfstudentid");
        String[] cfidcardno = request.getParameterValues("cfidcardno");
        String[] cfclazzid = request.getParameterValues("cfclazzid");
        String[] cfmobile = request.getParameterValues("cfmobile");
        String[] cfamount = request.getParameterValues("cfamount");
        String[] cfsqzjcxdh = request.getParameterValues("cfsqzjcxdh");
        String[] cfreceived = request.getParameterValues("cfreceived");
        String[] cffavorable = request.getParameterValues("cffavorable");
        String[] cfarrearage = request.getParameterValues("cfarrearage");
        String[] cfkmekssj = request.getParameterValues("cfkmekssj");
        String[] cfkmsbjksj = request.getParameterValues("cfkmsbjksj");
        String[] cfremark = request.getParameterValues("cfremark");
        String[] states = request.getParameterValues("states");
        String[] cfkscs = request.getParameterValues("cfkscs");
        String[] cfcoachname = request.getParameterValues("cfcoachname");
        String[] cfcoachid = request.getParameterValues("cfcoachid");

        //删除掉记录
        sharecarsubService.deletesharecarsubBydelfidAndLcsjid(delfid);
        if (cfstudentid == null) {
            return "redirect:/DrivingSys/practiseOrderAddList?alertType=bc";
        }

        for (int i = 0; i < cfstudentid.length; i++) {

            sharecarsub.setFid(fid[i]);
            sharecarsub.setCfstudentname(cfstudentname[i]);
            sharecarsub.setCfstudentid(cfstudentid[i]);
            sharecarsub.setCfmobile(cfmobile[i]);
            sharecarsub.setCfclazzid(cfclazzid[i]);
            sharecarsub.setCfidcardno(cfidcardno[i]);
            sharecarsub.setCfsqzjcxdh(cfsqzjcxdh[i]);
            sharecarsub.setCfamount(new BigDecimal(NullToBigDecimal(cfamount[i])));
            sharecarsub.setCfreceived(new BigDecimal(NullToBigDecimal(cfreceived[i])));
            sharecarsub.setCffavorable(new BigDecimal(NullToBigDecimal(cffavorable[i])));
            sharecarsub.setCfarrearage(new BigDecimal(NullToBigDecimal(cfarrearage[i])));
            sharecarsub.setCfkmekssj(cfkmekssj[i]);
            sharecarsub.setCfkmsbjksj(cfkmsbjksj[i]);
            sharecarsub.setCfremark(cfremark[i]);
            sharecarsub.setCfkscs(Long.valueOf(cfkscs[i] == "" ? "0" : cfkscs[i]));
            if( cfcoachname != null ) {
                sharecarsub.setCfcoachname(cfcoachname[i]);
            }
            if(  cfcoachid != null) {
                sharecarsub.setCfcoachid(cfcoachid[i]);
            }

            if (cfsqzjcxdh[i].equals("A2") || cfsqzjcxdh[i].equals("A3") || cfsqzjcxdh[i].equals("B1") || cfsqzjcxdh[i] == "B2") {
                sharecarsub.setCfcarbyte("2");
            } else {
                sharecarsub.setCfcarbyte("1");
            }

            if (fid == null || "".equals(fid[i])) {
                /*String number = customLogService.selectFunmber("CT_DS_SHARECARSUB", user.getFcompanyid());*/
                String fnumber = GetFnumber.fnumbers("YYFC");
                sharecarsub.setFnumber(fnumber);
                //预约成功与失败的批量添加
                if ("1".equals(states[i])) {
                    sharecarsubService.addsharecarsub(sharecarsub, null, null, cflcsjid, "3");
                } else if ("2".equals(states[i])) {
                    sharecarsubService.addsharecarsub(sharecarsub, null, null, cflcsjid, "4");
                }
            }
        }

        //添加日志
        customLogService.addLog("练车管理(批量)", TimeFormat.timeFormat(new Date()) + "对练车预约批量新增", null, user.getFid());

        return "redirect:/DrivingSys/practiseOrderAddList?alertType=bc";
    }


    /**
     * 练车预约(手动)
     **/

    /**
     * @Author lcx
     * @Date 2017/12/29 0029 下午 3:51
     * <p>
     * 上传
     */
    @ResponseBody
    @RequestMapping(value = "/uploadPractiseOrderMass")
    public Map<Object, Object> uploadPractiseOrderMass(@RequestParam(value="file")MultipartFile file,String state,
                                                              HttpServletRequest request,String carType,@RequestParam("cfbilltype")String cfbilltype, @RequestParam("studentName")String[]studentName,HttpServletResponse response) {
       List<Student> studentList = new ArrayList<>();
        List<Student> studentListError = new ArrayList<>();
        Map<Object, Object> studentsList = new HashMap<>();
        String filesFileName = file.getOriginalFilename();
        try {
            uploadExcel(request, file, filesFileName);// 上传
        } catch (Exception e) {
            e.printStackTrace();
        }
        Map<String, List<StudentImport>> map = new HashMap<>();
        //System.out.println("文件名字: "+request.getServletContext().getRealPath("/")+ "xls" + File.separatorChar + filesFileName);
        //读取
        map = poi.show4(
                request.getServletContext().getRealPath("/") + "xls" + File.separatorChar + filesFileName
        );
        Map<String, List<StudentImport>> map2=removeMapEmptyValue(map);
        map.clear();
        if (map2.size() == 0) {//没有可以导入的数据！";

            return null;
        } else {
            List<StudentImport> sList = map2.get("1");
            for (StudentImport studentImport : sList) {
                Student student = new Student();
                //c查询需要的数据
                List<Student> students = studentMapper.selectStudentByCfidcardno(studentImport.getCerno().toString().trim());
                String studentState = "";
                //对教练编码字符串进行校正  rhc
                String jlNoold=studentImport.getYyjlNo();
                String [] num=jlNoold.split("[.]");;
                String jlNonew=num[0];
                //根据教练编码查询教练id
                Person person = personService.selectPersonAsNo(jlNonew);
                if (students.size() != 0) {
                    studentState = students.get(0).getCfstudentstate().toString();
                }
                if (students == null || students.size() == 0 || "".equals(studentImport.getCerno()) || "".equals(studentImport.getStuName())) {
                    studentImport.setResult("您输入的身份证号码有误,或姓名为空");
                    studentListError.add(studentImport);

                } else if ("2".equals(studentState) || "5".equals(studentState) || "7".equals(studentState)
                        || "17".equals(studentState) || "19".equals(studentState) || "20".equals(studentState)
                        || "21".equals(studentState)) {
                    Student tem = students.get(0);
                    student = studentMapper.selectStudentById(tem.getFid());
                    if (person != null){
                        student.setJlid(person.getFid());
                        //可能由于是Object类因为，直接用toString方法
                        String jJname=person.getFnameL2().toString();
                        student.setJlname(jJname);
                        int check =0;
                        for(int i=0;i<studentName.length;i++){
                            if(studentName[i].equals(studentImport.getStuName())){
                               studentList.add(student);
                                check++;
                            }
                        }
                        if(check==0){
                        studentImport.setResult("您添加的学员已经重复");
                        studentListError.add(studentImport);
                        }
                    }else{
                        if("自考".equals(studentImport.getYyjlName())){
                            List<Person> personRandom =personService.selectPersonByrandom(cfbilltype);
                            String jJname=personRandom.get(0).getFnameL2().toString();
                            student.setJlname(jJname);
                            int check =0;
                            for(int i=0;i<studentName.length;i++){
                                if(studentName[i].equals(studentImport.getStuName())){
                                    studentList.add(student);
                                    check++;
                                }
                            }
                            if(check==0){
                                studentImport.setResult("您添加的学员已经重复");
                                studentListError.add(studentImport);
                            }
                        }else {
                        studentImport.setResult("教练编码有误");
                        studentListError.add(studentImport);
                        }
                    }




                } else if ("2".equals(studentState) || "4".equals(studentState) || "5".equals(studentState)
                        || "7".equals(studentState) || "8".equals(studentState) || "20".equals(studentState)
                        || "21".equals(studentState)) {
                    Student tem = students.get(0);
                    student = studentMapper.selectStudentById(tem.getFid());
                    if (person != null){
                        student.setJlid(person.getFid());
                        //对于Object可以采用toString的方法让其变为String类
                        student.setJlname(person.getFnameL2().toString());
//                        if(carType.equals(studentImport.getKscx())){
                       //没问题
                        int check =0;
                        for(int i=0;i<studentName.length;i++){
                            if(studentName[i].equals(studentImport.getStuName())){
                                studentList.add(student);
                                check++;
                            }
                        }
                        if(check==0){
                            studentImport.setResult("您添加的学员已经重复");
                            studentListError.add(studentImport);
                        }
                    }else{
                        if("自考".equals(studentImport.getYyjlName())){
                            List<Person> personRandom =personService.selectPersonByrandom(cfbilltype);
                            String jJname=personRandom.get(0).getFnameL2().toString();
                            student.setJlname(jJname);
                            int check =0;
                            for(int i=0;i<studentName.length;i++){
                                if(studentName[i].equals(studentImport.getStuName())){
                                    studentList.add(student);
                                    check++;
                                }
                            }
                            if(check==0){
                                studentImport.setResult("您添加的学员已经重复");
                                studentListError.add(studentImport);
                            }
                        }else {
                            studentImport.setResult("教练编码有误");
                            studentListError.add(studentImport);
                        }
                    }
                }else{
                    studentImport.setResult("学员状态有问题");
                    studentListError.add(studentImport);

                }

            }
        }

        studentsList.put("errorMsr", studentListError);
        studentsList.put("studentsNameList", studentList);
        return studentsList;
    }

    /**
     * 方式二：rhc
     * 删除掉map里面value为空的数据
     * @param paramMap
     * @return
     */
    /**
     *
      * @param paramMap
     * @return
     */
    public  Map<String, List<StudentImport>> removeMapEmptyValue(Map<String, List<StudentImport>> paramMap){
        Set<String> set = paramMap.keySet();
        Iterator<String> it = set.iterator();

        List<StudentImport> sList=new ArrayList<>();
        List<StudentImport> sList2=new ArrayList<>();
        Map<String, List<StudentImport>> paramMap2= new HashMap<>();
        sList=paramMap.get("1");
        for(StudentImport studentImport:sList){
            if(studentImport!=null&&!"".equals(studentImport.getStuName())){
                sList2.add(studentImport);
            }
        }
        paramMap2.put("1",sList2);
        return paramMap2;
    }


    /**
     * @Author lcx
     * @Date 2018/1/2 0002 上午 8:47
     * 导出表格
     */
    @RequestMapping(value = "/exportPractiseOrderMassExcle")
    public void exportExamOrderMass(HttpServletResponse response) throws IOException {
        String fileName = "分车预约导入模板.xls";
        fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;filename="
                + fileName);// 指定下载的文件名
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建单元格样式
        HSSFCellStyle cellStyleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行
        cellStyleTitle.setWrapText(true);
        cellStyleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyleTitle.setFillForegroundColor(HSSFColor.LAVENDER.index);
        // ------------------------------------------------------------------
        /*HSSFCellStyle cellStyle = wb.createCellStyle();
        // 指定单元格居中对齐
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定当单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        cellStyle.setFillForegroundColor((short)41);*/
        // ------------------------------------------------------------------
        // 设置单元格字体
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyleTitle.setFont(font);


        // 工作表名
        String id = "姓名";
        String name = "身份证号码";
        String yyjlNo = "预约教练编号";
        String yyjlName = "预约教练姓名";
        String bz = "备注";

        HSSFSheet sheet = wb.createSheet();
        /* ExcelExport exportExcel = new ExcelExport(wb, sheet);  */
        // 创建报表头部
        /*exportExcel.createNormalHead(worksheetTitle, 2);  */
        // 定义第一行
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell1 = row1.createCell(0);

        //第一行第一列

        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(id));
        //第一行第er列
        cell1 = row1.createCell(1);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(name));

        //第一行第3列
        cell1 = row1.createCell(2);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(yyjlNo));

        //第一行第4列
        cell1 = row1.createCell(3);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(yyjlName));

        //第一行第5列
        cell1 = row1.createCell(4);
        cell1.setCellStyle(cellStyleTitle);
        cell1.setCellValue(new HSSFRichTextString(bz));

        sheet.setColumnWidth(0, id.getBytes().length * 3 * 256);
        sheet.setColumnWidth(1, name.getBytes().length * 3 * 256);
        sheet.setColumnWidth(2, yyjlNo.getBytes().length * 3 * 256);
        sheet.setColumnWidth(3, yyjlName.getBytes().length * 3 * 256);
        sheet.setColumnWidth(4, bz.getBytes().length * 3 * 256);

      /*  //定义第二行
        HSSFRow row = sheet.createRow(2);
        HSSFCell cell = row.createCell(1);  */
        try {
            bufferedOutPut.flush();
            wb.write(bufferedOutPut);
            bufferedOutPut.close();
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("Output   is   closed ");
        } finally {

        }
    }

    public void uploadExcel(HttpServletRequest request, MultipartFile file, String fileName) throws Exception {
        String path = request.getServletContext().getRealPath("/") + "xls/";
        File fileUpload = new File(path);
        if (!fileUpload.exists()) {
            fileUpload.mkdirs();
        }
        file.transferTo(new File(path + fileName));
    }

    @RequestMapping("/querySharecarsubentry/look.action")
    public String querySharecarsubentry(HttpServletRequest request, @RequestParam(value = "cflcsj", defaultValue = "") String cflcsj,
                                        @RequestParam(value = "cfstudentname", defaultValue = "") String cfstudentname
            , @RequestParam(value = "cfidcardno", defaultValue = "") String cfidcardno
            , @RequestParam(value = "cfmobile", defaultValue = "") String cfmobile
            , @RequestParam(value = "subject", defaultValue = "2") String subject
            , @RequestParam(value = "beginTime", defaultValue = "") String beginTime
            , @RequestParam(value = "endTime", defaultValue = "") String endTime
            , @RequestParam(value = "state", defaultValue = "") String state
            , @RequestParam(value = "alertType", defaultValue = "") String alertType) {


        PageBean<Sharecarsub> pb = new PageBean<>();
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        try {
            cflcsj = cflcsj.trim();//去空格
            cfstudentname = cfstudentname.trim();//去空格
            cfidcardno = cfidcardno.trim();
            cfmobile = cfmobile.trim();
            //第一次进来
            if ("first".equals(state)) {
                beginTime = TimeFormat.getMonth();
                endTime = TimeFormat.getNowTimeAndOne();
                pb = getPageBean(request, user.getFcompanyid(), cflcsj, cfstudentname, cfidcardno, cfmobile, subject, beginTime, endTime, "");
            } else if ("".equals(state) && "".equals(beginTime) || "".equals(endTime)) {
                pb = getPageBean(request, user.getFcompanyid(), cflcsj, cfstudentname, cfidcardno, cfmobile, subject, beginTime, endTime, null);
            } else {
                pb = getPageBean(request, user.getFcompanyid(), cflcsj, cfstudentname, cfidcardno, cfmobile, subject, beginTime, endTime, "");
            }
            //获取当前分页的值
            List<Sharecarsub> sharecarsubList = pb.getBeanList();
            //设置信息总数的展现
            String messageAmount="共有"+pb.getTr()+"条信息";
            //返回界面的搜索条件
            request.setAttribute("messageAmount",messageAmount);
            request.setAttribute("cflcsj", cflcsj);
            request.setAttribute("cfstudentname", cfstudentname);
            request.setAttribute("cfidcardno", cfidcardno);
            request.setAttribute("cfmobile", cfmobile);
            request.setAttribute("subject", subject);
            request.setAttribute("beginTime", beginTime);
            request.setAttribute("endTime", endTime);
            request.setAttribute("sharecarsubList", sharecarsubList);
            request.setAttribute("pb", pb);
            request.setAttribute("alertType", alertType);


        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "/backHome/orderManage/practiseOrder/practiseOrderList";
    }

    public PageBean<Sharecarsub> getPageBean(HttpServletRequest request, String companyId, String cflcsj, String cfstudentname,
                                             String cfidcardno, String cfmobile, String subject,
                                             String beginTime, String endTime,
                                             String state) throws Exception {

        int offset;//从第几条数据开始加载
        if (request.getParameter("pager.offset") == null) {
            offset = 0;
        } else {
            offset = Integer.parseInt(request.getParameter("pager.offset"));
        }

        //拼接SQL语句
        StringBuffer psql = new StringBuffer();
        if (state == null) {
            psql.append("SELECT T.FID, e.fname_l2 kmelcsj,e.fname_l2 kmslcsj,t.CFSUBJECT,t.CFSQZJCXDH,t.FNUMBER,s.FNUMBER xsfnumber,t.CFSTUDENTNAME, " +
                    "  t.CFVIP,t.CFSUBRESULT,t.CFMOBILE,t.CFPHONE,r.FNUMBER yyjlfnumber,t.CFCOACHNAME,t.CFREMARK,t.CFCARBYTE,to_char(t.CFNEXTTIME, 'YYYY-MM-DD') cfnexttime,t.CFARREARAGE, " +
                    "  s1.FNAME_L2 zxsName,t1.CFSTUDENTNO zxsfnumber,c.FNAME_L2 classname,t.CFAMOUNT,t.CFRECEIVED,t.CFFAVORABLE,r1.FNAME_L2 yjlname, " +
                    "  to_char(t.CFKMEKSSJ, 'YYYY-MM-DD') cfkmekssj,to_char(t.CFKMSBJKSJ, 'YYYY-MM-DD') cfkmsbjksj,t.CFIDCARDNO,t.CFJBKCS,t.CFJBKFJE,t.CFKSCS " +
                    "  FROM CT_DS_SHARECARSUB t LEFT JOIN ct_ds_student s ON s.fid = t.cfstudentid " +
                    "  LEFT JOIN ct_ds_exercisecarplan e ON e.fid = t.CFEXERCISECARPLANI " +
                    "  LEFT JOIN CT_DS_PERSON r ON r.fid = t.CFCOACHID " +
                    "  LEFT JOIN CT_DS_SHARECARSUBENTRY t1 ON t.fid = t1.FPARENTID " +
                    "  LEFT JOIN ct_ds_clazz c ON t.CFCLAZZID = c.fid " +
                    "  LEFT JOIN CT_DS_STUDENT s1 ON s1.fid = t1.CFSTUDENTID " +
                    "  LEFT JOIN CT_DS_PERSON r1 ON t.CFOLDCOACHID = r1.FID");
            psql.append(" where t.FCONTROLUNITID= '" + companyId + "' AND (t.cfsubject = 3 OR t.cfsubject = 2) and t.CFCOMPUTATIONTIMESTATE is null");
            if (!"".equals(cflcsj) && cflcsj != null) {
                psql.append("  and e.fname_l2 LIKE '%" + cflcsj + "%' ");
            }
            if (!"".equals(cfstudentname) && cfstudentname != null) {
                psql.append("  and t.CFSTUDENTNAME LIKE '%" + cfstudentname + "%' ");
            }
            if (!"".equals(cfidcardno) && cfidcardno != null) {
                psql.append("  and t.CFIDCARDNO LIKE '%" + cfidcardno + "%' ");
            }
            if (!"".equals(cfmobile) && cfmobile != null) {
                psql.append("  and  t.CFMOBILE LIKE '%" + cfmobile + "%' ");
            }
            if (!"".equals(subject) && subject != null) {
                psql.append("  and t.CFSUBJECT = '" + subject + "'");
            }
            //todo 这里搜索是否需要预约时间暂定
            psql.append(" ORDER BY t.FCREATETIME DESC");
        } else {
            psql.append("SELECT T.FID, e.fname_l2 kmelcsj,e.fname_l2 kmslcsj,t.CFSUBJECT,t.CFSQZJCXDH,t.FNUMBER,s.FNUMBER xsfnumber,t.CFSTUDENTNAME, " +
                    "  t.CFVIP,t.CFSUBRESULT,t.CFMOBILE,t.CFPHONE,r.FNUMBER yyjlfnumber,t.CFCOACHNAME,t.CFREMARK,t.CFCARBYTE,to_char(t.CFNEXTTIME, 'YYYY-MM-DD') cfnexttime,t.CFARREARAGE, " +
                    "  s1.FNAME_L2 zxsName,t1.CFSTUDENTNO zxsfnumber,c.FNAME_L2 classname,t.CFAMOUNT,t.CFRECEIVED,t.CFFAVORABLE,r1.FNAME_L2 yjlname, " +
                    "  to_char(t.CFKMEKSSJ, 'YYYY-MM-DD') cfkmekssj,to_char(t.CFKMSBJKSJ, 'YYYY-MM-DD') cfkmsbjksj,t.CFIDCARDNO,t.CFJBKCS,t.CFJBKFJE,t.CFKSCS " +
                    "  FROM CT_DS_SHARECARSUB t LEFT JOIN ct_ds_student s ON s.fid = t.cfstudentid " +
                    "  LEFT JOIN ct_ds_exercisecarplan e ON e.fid = t.CFEXERCISECARPLANI " +
                    "  LEFT JOIN CT_DS_PERSON r ON r.fid = t.CFCOACHID " +
                    "  LEFT JOIN CT_DS_SHARECARSUBENTRY t1 ON t.fid = t1.FPARENTID " +
                    "  LEFT JOIN ct_ds_clazz c ON t.CFCLAZZID = c.fid " +
                    "  LEFT JOIN CT_DS_STUDENT s1 ON s1.fid = t1.CFSTUDENTID " +
                    "  LEFT JOIN CT_DS_PERSON r1 ON t.CFOLDCOACHID = r1.FID");
            psql.append(" where t.FCONTROLUNITID= '" + companyId + "' AND (t.cfsubject = 3 OR t.cfsubject = 2) and t.CFCOMPUTATIONTIMESTATE is null");

            if (!"".equals(cflcsj) && cflcsj != null) {
                psql.append("  and e.fname_l2 LIKE '%" + cflcsj + "%' ");
            }
            if (!"".equals(cfstudentname) && cfstudentname != null) {
                psql.append("  and t.CFSTUDENTNAME LIKE '%" + cfstudentname + "%' ");
            }
            if (!"".equals(cfidcardno) && cfidcardno != null) {
                psql.append("  and t.CFIDCARDNO LIKE '%" + cfidcardno + "%' ");
            }
            if (!"".equals(cfmobile) && cfmobile != null) {
                psql.append("  and  t.CFMOBILE LIKE '%" + cfmobile + "%' ");
            }
            if (!"".equals(subject) && subject != null) {
                psql.append("  and t.CFSUBJECT = '" + subject + "'");
            }
            //todo 这里搜索是否需要预约时间暂定

            psql.append(" and  (t.FCREATETIME BETWEEN to_date('" + beginTime + "', 'YYYY/MM/DD') AND to_date('" + endTime + "', 'YYYY/MM/DD')) ORDER BY t.FCREATETIME DESC");

        }

        int psize = 100; //每页显示数
        int pindex = offset / psize;//+1  页码从0开始
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = sharecarsubService.selectPage(pindex, psql.toString(), psize);
        PageBean<Sharecarsub> pb = new PageBean<>();
        pb.setPc(pindex); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql.toString()); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        pb.setBeanList((List<Sharecarsub>) paramMap.get("v_cur")); //每页数据集
        return pb;
    }

    /**
     * @Author lcx
     * @Date 2018/1/13 0013 上午 10:48
     * 学员自主约车的信息查询
     */
    public PageBean<Sharecarsub> getPageBean2(HttpServletRequest request, String companyId, String cflcsj, String cfstudentname,
                                              String cfidcardno, String cfmobile, String cfcomputationtimestate, String subject,
                                              String beginTime, String endTime,
                                              String state) throws Exception {

        int offset;//从第几条数据开始加载
        if (request.getParameter("pager.offset") == null) {
            offset = 0;
        } else {
            offset = Integer.parseInt(request.getParameter("pager.offset"));
        }

        //拼接SQL语句
        StringBuffer psql = new StringBuffer();
        if (state == null) {
            psql.append("SELECT T.FID, e.fname_l2 kmelcsj,e.fname_l2 kmslcsj,t.CFSUBJECT,t.CFSQZJCXDH,t.FNUMBER,s.FNUMBER xsfnumber,t.CFSTUDENTNAME, " +
                    "  t.CFVIP,t.CFSUBRESULT,t.CFMOBILE,t.CFPHONE,r.FNUMBER yyjlfnumber,t.CFCOACHNAME,t.CFREMARK,t.CFCARBYTE,to_char(t.CFNEXTTIME, 'YYYY-MM-DD') cfnexttime,t.CFARREARAGE, " +
                    "  s1.FNAME_L2 zxsName,t1.CFSTUDENTNO zxsfnumber,c.FNAME_L2 classname,t.CFAMOUNT,t.CFRECEIVED,t.CFFAVORABLE,r1.FNAME_L2 yjlname, " +
                    "  to_char(t.CFKMEKSSJ, 'YYYY-MM-DD') cfkmekssj,to_char(t.CFKMSBJKSJ, 'YYYY-MM-DD') cfkmsbjksj,t.CFIDCARDNO,t.CFJBKCS,t.CFJBKFJE,t.CFKSCS, " +
                    "  to_char(t.CFBEGINTIME, 'yyyy-mm-dd HH24:mi:ss') CFBEGINTIME,  to_char(t.CFENDTIME, 'yyyy-mm-dd HH24:mi:ss') CFENDTIME,to_char( e2.CFEXERCISEDATE, 'yyyy-mm-dd HH24:mi:ss') cfexercisecarplanentryBegin,to_char( e2.CFENDTIME, 'yyyy-mm-dd HH24:mi:ss') cfexercisecarplanentryEnd " +
                    "  FROM CT_DS_SHARECARSUB t LEFT JOIN ct_ds_student s ON s.fid = t.cfstudentid " +
                    "  LEFT JOIN ct_ds_exercisecarplan e ON e.fid = t.CFEXERCISECARPLANI " +
                    "  LEFT JOIN CT_DS_PERSON r ON r.fid = t.CFCOACHID " +
                    "  LEFT JOIN CT_DS_SHARECARSUBENTRY t1 ON t.fid = t1.FPARENTID " +
                    "  LEFT JOIN ct_ds_clazz c ON t.CFCLAZZID = c.fid " +
                    "  LEFT JOIN CT_DS_STUDENT s1 ON s1.fid = t1.CFSTUDENTID " +
                    "  LEFT JOIN CT_DS_PERSON r1 ON t.CFOLDCOACHID = r1.FID" +
                    "  left join CT_DS_EXERCISECARPLANENTRY e2 on e2.fid = t.CFEXERCISECARPLANENTRYID");
            psql.append(" where t.FCONTROLUNITID= '" + companyId + "' AND (t.cfsubject = 3 OR t.cfsubject = 2)  and t.CFCOMPUTATIONTIMESTATE ='" + cfcomputationtimestate + "'");
            if (!"".equals(cflcsj) && cflcsj != null) {
                psql.append("  and e.fname_l2 LIKE '%" + cflcsj + "%' ");
            }
            if (!"".equals(cfstudentname) && cfstudentname != null) {
                psql.append(" and t.CFSTUDENTNAME LIKE '%" + cfstudentname + "%' ");
            }
            if (!"".equals(cfidcardno) && cfidcardno != null) {
                psql.append(" and t.CFIDCARDNO LIKE '%" + cfidcardno + "%' ");
            }
            if (!"".equals(cfmobile) && cfmobile != null) {
                psql.append(" and  t.CFMOBILE LIKE '%" + cfmobile + "%' ");
            }
            if (!"".equals(subject) && subject != null) {
                psql.append("  and t.CFSUBJECT = '" + subject + "'");
            }
            psql.append(" ORDER BY t.FCREATETIME DESC");
        } else {

            psql.append("SELECT T.FID, e.fname_l2 kmelcsj,e.fname_l2 kmslcsj,t.CFSUBJECT,t.CFSQZJCXDH,t.FNUMBER,s.FNUMBER xsfnumber,t.CFSTUDENTNAME, " +
                    "  t.CFVIP,t.CFSUBRESULT,t.CFMOBILE,t.CFPHONE,r.FNUMBER yyjlfnumber,t.CFCOACHNAME,t.CFREMARK,t.CFCARBYTE,to_char(t.CFNEXTTIME, 'YYYY-MM-DD') cfnexttime,t.CFARREARAGE, " +
                    "  s1.FNAME_L2 zxsName,t1.CFSTUDENTNO zxsfnumber,c.FNAME_L2 classname,t.CFAMOUNT,t.CFRECEIVED,t.CFFAVORABLE,r1.FNAME_L2 yjlname, " +
                    "  to_char(t.CFKMEKSSJ, 'YYYY-MM-DD') cfkmekssj,to_char(t.CFKMSBJKSJ, 'YYYY-MM-DD') cfkmsbjksj,t.CFIDCARDNO,t.CFJBKCS,t.CFJBKFJE,t.CFKSCS, " +
                    "  to_char(t.CFBEGINTIME, 'yyyy-mm-dd HH24:mi:ss') CFBEGINTIME,  to_char(t.CFENDTIME, 'yyyy-mm-dd HH24:mi:ss') CFENDTIME,to_char( e2.CFEXERCISEDATE, 'yyyy-mm-dd HH24:mi:ss') cfexercisecarplanentryBegin,to_char( e2.CFENDTIME, 'yyyy-mm-dd HH24:mi:ss') cfexercisecarplanentryEnd " +
                    "  FROM CT_DS_SHARECARSUB t LEFT JOIN ct_ds_student s ON s.fid = t.cfstudentid " +
                    "  LEFT JOIN ct_ds_exercisecarplan e ON e.fid = t.CFEXERCISECARPLANI " +
                    "  LEFT JOIN CT_DS_PERSON r ON r.fid = t.CFCOACHID " +
                    "  LEFT JOIN CT_DS_SHARECARSUBENTRY t1 ON t.fid = t1.FPARENTID " +
                    "  LEFT JOIN ct_ds_clazz c ON t.CFCLAZZID = c.fid " +
                    "  LEFT JOIN CT_DS_STUDENT s1 ON s1.fid = t1.CFSTUDENTID " +
                    "  LEFT JOIN CT_DS_PERSON r1 ON t.CFOLDCOACHID = r1.FID" +
                    "  left join CT_DS_EXERCISECARPLANENTRY e2 on e2.fid = t.CFEXERCISECARPLANENTRYID");
            /*AND (t.cfsubject = 3 OR t.cfsubject = 2)*/
            psql.append(" where t.FCONTROLUNITID= '" + companyId + "'   and t.CFCOMPUTATIONTIMESTATE ='" + cfcomputationtimestate + "'");
            if (!"".equals(cflcsj) && cflcsj != null) {
                psql.append("  and e.fname_l2 LIKE '%" + cflcsj + "%' ");
            }
            if (!"".equals(cfstudentname) && cfstudentname != null) {
                psql.append(" and t.CFSTUDENTNAME LIKE '%" + cfstudentname + "%' ");
            }
            if (!"".equals(cfidcardno) && cfidcardno != null) {
                psql.append(" and t.CFIDCARDNO LIKE '%" + cfidcardno + "%' ");
            }
            if (!"".equals(cfmobile) && cfmobile != null) {
                psql.append(" and  t.CFMOBILE LIKE '%" + cfmobile + "%' ");
            }
            if (!"".equals(subject) && subject != null) {
                psql.append("  and t.CFSUBJECT = '" + subject + "'");
            }
            //todo 这里搜索是否需要预约时间暂定
            psql.append("and  (t.FCREATETIME BETWEEN to_date('" + beginTime + "', 'YYYY/MM/DD') AND to_date('" + endTime + "', 'YYYY/MM/DD')) ORDER BY t.FCREATETIME DESC");

        }

        int psize = 15; //每页显示数
        int pindex = offset / psize;//+1  页码从0开始
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = sharecarsubService.selectPage(pindex, psql.toString(), psize);
        PageBean<Sharecarsub> pb = new PageBean<>();
        pb.setPc(pindex); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql.toString()); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        pb.setBeanList((List<Sharecarsub>) paramMap.get("v_cur")); //每页数据集
        return pb;
    }

    @RequestMapping(value = "/go_add_Sharecarsubentry")
    public String goAddDropexercisethree(HttpServletRequest request, String alertType) throws Exception {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        Department department = departmentService.queryAdminByFid(user.getFcompanyid());

        /*String number = customLogService.selectFunmber("CT_DS_SHARECARSUB",user.getFcompanyid());*/
        String fnumber = GetFnumber.fnumbers("YYFC");

        //查询练车时间
        Map map3 = exerciseCarPlanController.getExerciseCarPlan(request, "", "", "0");
        //查询科目二考试学生
        Map map = studentController.getPageBeanAsEnrol(request, "", "", "0", TimeFormat.getMonth(), TimeFormat.getNowTime(), "fcyy", 2, "", "");
        //查询教练弹窗信息
        Map map1 = personController.getPageAsJl(request, "", "", "0");


        request.setAttribute("beginTime", TimeFormat.getMonth());
        request.setAttribute("endTime", TimeFormat.getNowTime());


        request.setAttribute("flag", "1");
        request.setAttribute("fbizdate", TimeFormat.getNowTime());
        request.setAttribute("createName", user.getName());
        request.setAttribute("fnumber", fnumber);
        request.setAttribute("department", department.getFnameL2());
        request.setAttribute("pb3", map3.get("pb"));
        request.setAttribute("exerciseCarPlans", map3.get("sList"));
        request.setAttribute("pb", map.get("pb"));
        request.setAttribute("students", map.get("sList"));
        request.setAttribute("pb1", map1.get("pb"));
        request.setAttribute("persons", map1.get("sList"));
        request.setAttribute("time", TimeFormat.getNowTime());

        request.setAttribute("alertType", alertType);

        return "/backHome/orderManage/practiseOrder/detailPractiseOrder";

    }

    @RequestMapping(value = "/add_Sharecarsubentry")
    public String addSharecarsub(HttpServletRequest request, Sharecarsub sharecarsub, String cflcsjid,
                                 @RequestParam(defaultValue = "0") String state) {

        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        //公司id
        sharecarsub.setCfficompanyid(user.getFcompanyid());
        //控制单元id
        sharecarsub.setFcontrolunitid(user.getFcompanyid());
        //获取当前修改时间
        String createTime = TimeFormat.timeFormat(new Date());
        //创建时间
        sharecarsub.setFcreatetime(createTime);
        //创建人id
        sharecarsub.setFcreatorid(user.getFid());


        //获取子类
        String[] zxsName = request.getParameterValues("zxsNameid");//学生id
        String[] zxsfnumber = request.getParameterValues("zxsfnumber");

        sharecarsubService.addsharecarsub(sharecarsub, zxsName, zxsfnumber, cflcsjid, state);


        //添加日志
        customLogService.addLog("练车管理", TimeFormat.timeFormat(new Date()) + "对科目二退练新增", null, user.getFid());


        return "redirect:/DrivingSys/go_add_Sharecarsubentry?alertType=bc";
    }

    @RequestMapping(value = "go_update_Sharecarsubentry")
    public String goUpdateShareCarThreeReg(HttpServletRequest request, String fid, String state) throws Exception {

        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        Department department = departmentService.queryAdminByFid(user.getFcompanyid());

        //查询练车时间
        Map map3 = exerciseCarPlanController.getExerciseCarPlan(request, "", "", "0");
        //查询科目二考试学生
        Map map = studentController.getPageBeanAsEnrol(request, "", "", "0", TimeFormat.getMonth(), TimeFormat.getNowTime(), "fcyy", 2, "", "");
        //查询教练弹窗信息
        Map map1 = personController.getPageAsJl(request, "", "", "0");

        //查询模拟分车信息
        Sharecarsub sharecarsub = sharecarsubService.selectSharecarsubentryByFid(fid);
        List<Sharecarsubentry> sharecarsubentryList = sharecarsubService.selectSharecarsubentryByFparentid(fid);

        request.setAttribute("beginTime", TimeFormat.getMonth());
        request.setAttribute("endTime", TimeFormat.getNowTime());

        request.setAttribute("flag", "2");
        request.setAttribute("sharecarsub", sharecarsub);
        request.setAttribute("sharecarsubentryList", sharecarsubentryList);
        request.setAttribute("department", department.getFnameL2());
        request.setAttribute("pb3", map3.get("pb"));
        request.setAttribute("exerciseCarPlans", map3.get("sList"));
        request.setAttribute("pb", map.get("pb"));
        request.setAttribute("students", map.get("sList"));
        request.setAttribute("pb1", map1.get("pb"));
        request.setAttribute("persons", map1.get("sList"));

        request.getSession().setAttribute("fid",sharecarsub.getCfstudentid());

        //如果是批量练车传入的界面 则保存 练车时间id
        if ("1".equals(state)) {
            request.setAttribute("cflcsjid", sharecarsub.getCfexercisecarplani());
            return "/backHome/orderManage/practiseOrder/detailPractiseOrder1";
        }

        return "/backHome/orderManage/practiseOrder/detailPractiseOrder";
    }

    /**
     * @Author lcx
     * @Date 2017/12/6 0006 下午 5:05
     * 删除
     */
    @RequestMapping(value = "delete_Sharecarsubentry")
    public String deleteDropexercisethree(HttpServletRequest request, String[] deleteId, String state) throws Exception {

        User user = (User) request.getSession().getAttribute("user");

        sharecarsubService.deleteBySharecarsubentryKey(deleteId);
        //添加日志
        customLogService.addLog("预约管理", TimeFormat.timeFormat(new Date()) + "对练车预约进行删除", null, user.getFid());

        if ("1".equals(state)) {
            return "redirect:/DrivingSys/querySharecarsubentryAsWx/look.action?state=first";
        }

        return "redirect:/DrivingSys/querySharecarsubentry/look.action?state=first";
    }

    @RequestMapping(value = "/update_Sharecarsub")
    public String updateSharecarsubentry(HttpServletRequest request, Sharecarsub sharecarsub, String cflcsjid, String state) throws Exception {

        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");

        //获取当前修改时间
        String createTime = TimeFormat.timeFormat(new Date());
        //修改时间
        sharecarsub.setFlastupdatetime(createTime);
        //修改人id
        sharecarsub.setFlastupdateuserid(user.getFid());

        //如果传入状态为5则代表审核
        if ("5".equals(sharecarsub.getFfivouchered() + "")) {
            sharecarsub.setFauditorid(user.getFid());
        }

        addSharecarsub(request, sharecarsub, cflcsjid, "1");

        if ("1".equals(state)) {

            SharecarsubView sharecarsubView = (SharecarsubView) request.getSession().getAttribute("sharecarsubView");
            practiseOrderAdd(request, sharecarsubView.getCfdycs(), cflcsjid, sharecarsubView.getCfbilltype(), sharecarsubView.getCfsqzjcxdh(), sharecarsubView.getCfqty());

        }

        return "redirect:/DrivingSys/querySharecarsubentry/look.action?state=first&alertType=qd";
    }

    /**
     * @Author lcx
     * @Date 2018/1/13 0013 上午 10:34
     * <p>
     * 学员自主约车搜索
     */
    @RequestMapping("/querySharecarsubentryAsWx/look.action")
    public String querySharecarsubentryAswx(HttpServletRequest request, @RequestParam(value = "cflcsj", defaultValue = "") String cflcsj
            , @RequestParam(value = "cfstudentname", defaultValue = "") String cfstudentname
            , @RequestParam(value = "cfidcardno", defaultValue = "") String cfidcardno
            , @RequestParam(value = "cfmobile", defaultValue = "") String cfmobile
            , @RequestParam(value = "cfcomputationtimestate", defaultValue = "1") String cfcomputationtimestate
            , @RequestParam(value = "subject", defaultValue = "2") String subject
            , @RequestParam(value = "beginTime", defaultValue = "") String beginTime
            , @RequestParam(value = "endTime", defaultValue = "") String endTime
            , @RequestParam(value = "state", defaultValue = "") String state) {
        PageBean<Sharecarsub> pb = new PageBean<>();
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");
        try {
            cflcsj = cflcsj.trim();//去空格
            cfstudentname = cfstudentname.trim();//去空格
            cfidcardno = cfidcardno.trim();
            cfmobile = cfmobile.trim();
            //第一次进来
            if ("first".equals(state)) {
                beginTime = TimeFormat.getMonth();
                endTime = TimeFormat.getNowTimeAndOne();
                pb = getPageBean2(request, user.getFcompanyid(), cflcsj, cfstudentname, cfidcardno, cfmobile, cfcomputationtimestate, subject, beginTime, endTime, "");
            } else if ("".equals(state) && "".equals(beginTime) || "".equals(endTime)) {
                pb = getPageBean2(request, user.getFcompanyid(), cflcsj, cfstudentname, cfidcardno, cfmobile, cfcomputationtimestate, subject, beginTime, endTime, null);
            } else {
                pb = getPageBean2(request, user.getFcompanyid(), cflcsj, cfstudentname, cfidcardno, cfmobile, cfcomputationtimestate, subject, beginTime, endTime, "");
            }
            //获取当前分页的值
            List<Sharecarsub> sharecarsubList = pb.getBeanList();
            //返回界面的搜索条件
            request.setAttribute("cflcsj", cflcsj);
            request.setAttribute("cfstudentname", cfstudentname);
            request.setAttribute("cfidcardno", cfidcardno);
            request.setAttribute("cfmobile", cfmobile);
            request.setAttribute("subject", subject);
            request.setAttribute("beginTime", beginTime);
            request.setAttribute("endTime", endTime);
            request.setAttribute("cfcomputationtimestate", cfcomputationtimestate);

            request.setAttribute("sharecarsubList", sharecarsubList);
            request.setAttribute("pb", pb);


        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "/backHome/orderManage/calculatetimecarsubOrder/calculatetimecarsubOrderList";
    }


    /**
     * 取记录总数 dmz 20160921
     *
     * @param psql SQL语句
     * @return Map
     * @throws Exception
     */
    public Map<String, Object> getPageBeanCount(String psql) throws Exception {

        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = sharecarsubService.selectPageCount(psql);
        return paramMap;
    }


    @RequestMapping("/querySharecarsubentryXls")
    public String querySharecarsubentryXls(HttpServletRequest request, HttpServletResponse response,
                                           @RequestParam(defaultValue = "", value = "cflcsj") String cflcsj,
                                           @RequestParam(defaultValue = "", value = "cfstudentname") String cfstudentname,
                                           @RequestParam(defaultValue = "", value = "cfidcardno") String cfidcardno,
                                           @RequestParam(defaultValue = "", value = "cfmobile") String cfmobile,
                                           @RequestParam(defaultValue = "2", value = "subject") String subject,
                                           @RequestParam(defaultValue = "", value = "beginTime") String beginTime,
                                           @RequestParam(defaultValue = "", value = "endTime") String endTime) {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");

        //头部数据
        List<Object> data = new ArrayList<>();
        String head[] = {"练车时间", "科目", "申请准驾车型代号", "单据编号", "学员编号", "学员名称", "VIP", "预约结果", "手机", "电话"
                , "预约教练编码", "教练名称", "备注", "车型", "下一次预约时间", "欠费", "学员", "学员编码", "班级", "应收金额"
                , "已收金额", "优惠金额", "原教练", "科目二考试时间", "科目三不及格时间", "身份证号码", "交补考费次数", "交补考费总额", "考试次数"
        };
        //长度
        Integer length[] = {9000, 3000, 3000, 6000, 6000, 4000, 2000, 3000, 4000, 4000
                , 4000, 3000, 3000, 3000, 3000, 3000, 3000, 7000, 7000, 3000
                , 3000, 3000, 3000, 6000, 6000, 6000, 4000, 4000, 4000};

        List<Sharecarsub> sharecarsubList = sharecarsubService.selectSharecarsubentryXls(cflcsj, cfstudentname, cfidcardno, cfmobile, subject, beginTime, endTime, user.getFcompanyid());


        for (Sharecarsub sharecarsubentry : sharecarsubList) {
            data.add(sharecarsubentry.getKmelcsj());
            if ("2".equals(sharecarsubentry.getCfsubject())) {
                data.add("科目二");
            } else {
                data.add("科目三");
            }
            data.add(sharecarsubentry.getCfsqzjcxdh());//申请准驾车型代号
            data.add(sharecarsubentry.getFnumber());
            data.add(sharecarsubentry.getXsfnumber());
            data.add(sharecarsubentry.getCfstudentname());
            data.add(stateToString.cfstuvip(sharecarsubentry.getCfvip()));
            data.add(stateToString.cfsubresult(sharecarsubentry.getCfsubresult()));
            data.add(sharecarsubentry.getCfmobile());
            data.add(sharecarsubentry.getCfphone());
            data.add(sharecarsubentry.getYyjlfnumber());
            data.add(sharecarsubentry.getCfcoachname());
            data.add(sharecarsubentry.getCfremark());
            data.add(sharecarsubentry.getCfsqzjcxdh());
            data.add(sharecarsubentry.getCfnexttime());
            data.add(sharecarsubentry.getCfarrearage());
            data.add(sharecarsubentry.getZxsName());
            data.add(sharecarsubentry.getZxsfnumber());
            data.add(sharecarsubentry.getClassname());
            data.add(sharecarsubentry.getCfamount());
            data.add(sharecarsubentry.getCfreceived());
            data.add(sharecarsubentry.getCffavorable());
            data.add(sharecarsubentry.getYjlname());
            data.add(sharecarsubentry.getCfkmekssj());
            data.add(sharecarsubentry.getCfkmsbjksj());
            data.add(sharecarsubentry.getCfidcardno());
            data.add(sharecarsubentry.getCfjbkcs());
            data.add(sharecarsubentry.getCfjbkfje());
            data.add(sharecarsubentry.getCfkscs());
        }

        try {
            //导出Excel
            CommonExport.importExcel(response, "练车预约" + TimeFormat.getNowTime2() + ".xls", "练车预约", data, head, length);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "redirect:/DrivingSys/querySharecarsubentry/look.action?state=first";
    }

    @RequestMapping("/querySharecarsubentryAsWxXls")
    public String querySharecarsubentryAsWxXls(HttpServletRequest request, HttpServletResponse response,
                                               @RequestParam(defaultValue = "", value = "cflcsj") String cflcsj,
                                               @RequestParam(defaultValue = "", value = "cfstudentname") String cfstudentname,
                                               @RequestParam(defaultValue = "", value = "cfidcardno") String cfidcardno,
                                               @RequestParam(defaultValue = "", value = "cfmobile") String cfmobile,
                                               @RequestParam(defaultValue = "1", value = "cfcomputationtimestate") String cfcomputationtimestate,
                                               @RequestParam(defaultValue = "2", value = "subject") String subject,
                                               @RequestParam(defaultValue = "", value = "beginTime") String beginTime,
                                               @RequestParam(defaultValue = "", value = "endTime") String endTime) {
        //获取操作人信息
        User user = (User) request.getSession().getAttribute("user");

        //头部数据
        List<Object> data = new ArrayList<>();
        String head[] = {"练车时间", "时间范围", "开始时间", "结束时间", "科目", "申请准驾车型代号", "单据编号", "学员编号", "学员名称", "VIP"
                , "预约结果", "手机", "电话", "预约教练编码", "教练名称", "备注", "车型", "下一次预约时间", "欠费", "学员"
                , "学员编码", "班级", "应收金额", "已收金额", "优惠金额", "原教练", "科目二考试时间", "科目三不及格时间", "身份证号码", "交补考费次数"
                , "交补考费总额", "考试次数"
        };
        //长度
        Integer length[] = {9000, 11000, 6000, 6000, 3000, 3000, 9000, 6000, 4000, 2000,
                3000, 4000, 4000, 4000, 3000, 3000, 3000, 3000, 3000, 3000,
                7000, 7000, 3000, 3000, 3000, 3000, 6000, 6000, 6000, 4000,
                4000, 4000};

        List<Sharecarsub> sharecarsubList = sharecarsubService.selectSharecarsubentryAsWxXls(cflcsj, cfstudentname, cfidcardno, cfmobile, cfcomputationtimestate, subject, beginTime, endTime, user.getFcompanyid());


        for (Sharecarsub sharecarsubentry : sharecarsubList) {
            data.add(sharecarsubentry.getKmelcsj());
            data.add((sharecarsubentry.getCfexercisecarplanentryBegin() == null ? "" : sharecarsubentry.getCfexercisecarplanentryBegin())
                    + "——" + (sharecarsubentry.getCfexercisecarplanentryEnd() == null ? "" : sharecarsubentry.getCfexercisecarplanentryEnd()));
            data.add(sharecarsubentry.getCfbegintime());
            data.add(sharecarsubentry.getCfendtime());
            if ("2".equals(sharecarsubentry.getCfsubject())) {
                data.add("科目二");
            } else {
                data.add("科目三");
            }
            data.add(sharecarsubentry.getCfsqzjcxdh());//申请准驾车型代号
            data.add(sharecarsubentry.getFnumber());
            data.add(sharecarsubentry.getXsfnumber());
            data.add(sharecarsubentry.getCfstudentname());
            data.add(stateToString.cfstuvip(sharecarsubentry.getCfvip()));
            data.add(stateToString.cfsubresult(sharecarsubentry.getCfsubresult()));
            data.add(sharecarsubentry.getCfmobile());
            data.add(sharecarsubentry.getCfphone());
            data.add(sharecarsubentry.getYyjlfnumber());
            data.add(sharecarsubentry.getCfcoachname());
            data.add(sharecarsubentry.getCfremark());
            data.add(sharecarsubentry.getCfsqzjcxdh());
            data.add(sharecarsubentry.getCfnexttime());
            data.add(sharecarsubentry.getCfarrearage());
            data.add(sharecarsubentry.getZxsName());
            data.add(sharecarsubentry.getZxsfnumber());
            data.add(sharecarsubentry.getClassname());
            data.add(sharecarsubentry.getCfamount());
            data.add(sharecarsubentry.getCfreceived());
            data.add(sharecarsubentry.getCffavorable());
            data.add(sharecarsubentry.getYjlname());
            data.add(sharecarsubentry.getCfkmekssj());
            data.add(sharecarsubentry.getCfkmsbjksj());
            data.add(sharecarsubentry.getCfidcardno());
            data.add(sharecarsubentry.getCfjbkcs());
            data.add(sharecarsubentry.getCfjbkfje());
            data.add(sharecarsubentry.getCfkscs());
        }

        try {
            //导出Excel
            CommonExport.importExcel(response, "练车预约" + TimeFormat.getNowTime2() + ".xls", "练车预约", data, head, length);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "redirect:/DrivingSys/querySharecarsubentryAsWx/look.action?state=first";
    }


}
